from flask import Blueprint, request
from common.libs.Helper import ops_renderJSON, ops_renderErrJSON, iPagenation, ops_render
from common.models.user import User
from application import db, app
from common.libs.DataHelper import getCurrentTime, getCurrentDate
from flask import json
import datetime
from sqlalchemy import func
import requests
import urllib.request
import flask_excel as excel
import pandas as pd
from sqlalchemy import create_engine
from dateutil.relativedelta import relativedelta
from echarts.base_wordcloud import base_wordcloud
from echarts.base_pie import base_pie
from echarts.base_bar import base_bar
from datetime import date, timedelta
from application import ExoskeletonEngine as engine

meeting_page = Blueprint("meeting_page", __name__)

# 获得当前日期和一年前日期
edate = date.today().strftime("%Y-%m-%d")
sdate = (date.today() - timedelta(days=365)).strftime("%Y-%m-%d")

sql1 = "'''" + "select * from meeting_order where updated_time > " + "'" + sdate + "'" + ";" + "'''"
sql2 = ''' select * from user; '''
sql = eval(sql1)


sealdata = pd.read_sql_query(sql, engine)
userdata = pd.read_sql_query(sql2, engine).replace(["其他公司", "山西公司", "西北区域公司"], ["", "", ""])

userdata.rename(columns={'id': 'uid'}, inplace=True)
ordata = pd.merge(sealdata, userdata, on='uid')

ordata['year'] = ordata.updated_time_x.dt.year
ordata['month'] = ordata.updated_time_x.dt.month


@meeting_page.route("/index")
def meetingindex():
    date = datetime.date.today() - relativedelta(months=+1)
    datestr = date.strftime("%Y%m")
    return ops_render("/meeting/index.html", {"datestr": datestr, "part": "total"})

@meeting_page.route("/total")
def meetingtotal():
    date = request.args.get("date")
    return ops_render("/meeting/index.html", {"datestr": date, "part": "total"})


@meeting_page.route("/api_meeting_hy_pie")
def meeting_hy_pie():
    date = request.args.get("date")
    years = int(date[:4])
    month = int(date[4:])


    data = ordata[(ordata["year"] == years) & (ordata["month"] == month) & (ordata["city_x"] == "太原")].loc[:, [
                                                "department",
                                                "mark",

                                            ]]

    data = data.groupby(["department"]).agg(
        {"mark": 'count'}).reset_index()

    x = data["department"].values.tolist()
    y = data['mark'].values.tolist()

    showdata = base_pie(x=x, y1=y, title="（单位：次）")
    return showdata.dump_options_with_quotes()

@meeting_page.route("/api_meeting_hys_pie")
def meeting_hys_pie():
    date = request.args.get("date")
    years = int(date[:4])
    month = int(date[4:])


    data = ordata[(ordata["year"] == years) & (ordata["month"] == month)& (ordata["city_x"] == "太原") ].loc[:, [
                                                "meetingroom",
                                                "mark",

                                            ]]

    data = data.groupby(["meetingroom"]).agg(
        {"mark": 'count'}).reset_index()

    x = data["meetingroom"].values.tolist()
    y = data['mark'].values.tolist()

    showdata = base_pie(x=x, y1=y, title="（单位：次）")
    return showdata.dump_options_with_quotes()